package org.sction.manager.database;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.sction.util.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.transaction.annotation.Transactional;


/**
 * 基于JdbcTemplate封装数据库操作类
 * 
 * @author 作者 : sorc
 * @version 创建时间：2013-3-14 下午1:29:41
 * 
 * @see CurrentPage
 */

public class SpringDBHelper {
	private static final Logger log = Logger.getLogger(SpringDBHelper.class);

	private static JdbcTemplate jdbc;

	/*
	 * 是否显示sql执行信息
	 */
	private static boolean showSql = false;

	/**
	 * 设置Spring JdbcTemplate
	 * 
	 * @param jdbc
	 */
	public static void setJdbc(JdbcTemplate jdbc) {
		SpringDBHelper.jdbc = jdbc;
		if (jdbc != null) {
			log.info("SpringDBHelper OK!");
			try {
				log.info(jdbc.getDataSource().getConnection().getMetaData()
						.getCatalogTerm());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static JdbcTemplate getJdbc() {
		return jdbc;
	}

	/**
	 * 设置是否显示 执行的sql语句
	 * 
	 * @param showSql
	 *            默认false
	 */
	public static void setShowSql(boolean showSql) {
		SpringDBHelper.showSql = showSql;
	}

	/**
	 * 私有方法 将条件MAP参数转换为sql预定义语句 并通过引用方式返回参数列表
	 * 
	 * @param map
	 *            条件MAP参数
	 * @param params
	 *            引用方式返回参数列表
	 * @return
	 */
	private static String toWhereSql(Map<String, Object> map,
			List<Object> params) {
		String sql = "";
		if (map != null) {
			sql += " where ";
			Set<String> keys = map.keySet();
			for (String key : keys) {
				Object value = map.get(key);
				if (value == null) {
					sql += key + " is null and ";
				} else {
					sql += key + "=? and ";
					params.add(map.get(key));
				}
			}
			if (sql.endsWith(" and ")) {
				sql = sql.substring(0, sql.length() - 5) + "";
			}
			if (sql.endsWith(" where ")) {
				sql = sql.substring(0, sql.length() - 7) + "";
			}
		}
		return sql;
	}

	/**
	 * 私有方法 将条件MAP参数中键符合条件字段名的字段 转换为sql预定义语句 并通过引用方式返回参数列表
	 * 
	 * @param map
	 *            条件MAP参数
	 * @param params
	 *            引用方式返回参数列表
	 * @param pkeys
	 *            条件字段名
	 * @return
	 */
	private static String toWhereSql(Map<String, Object> map,
			List<Object> params, String... pkeys) {
		String sql = "";
		if (map != null) {
			sql += " where ";
			for (int i = 0; i < pkeys.length; i++) {
				String key = pkeys[i];
				Object value = map.get(key);
				if (value == null) {
					sql += key + " is null and ";
				} else {
					sql += key + "=? and ";
					params.add(map.get(key));
				}
			}
			if (sql.endsWith(" and ")) {
				sql = sql.substring(0, sql.length() - 5) + "";
			}
			if (sql.endsWith(" where ")) {
				sql = sql.substring(0, sql.length() - 7) + "";
			}
		}
		return sql;
	}

	/**
	 * 查询单条记录方法 可能存在多条符合的结果 仅取第一条
	 * 
	 * @param tablename
	 *            表名
	 * @param map
	 *            条件参数MAP 全部满足
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Transactional()
	public static Map<String, Object> findForMap(String tablename,
			Map<String, Object> map) {
		List<Object> params = new ArrayList<Object>();
		String sql = "select top 1 * from " + tablename
				+ toWhereSql(map, params);
		try {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + params);
			}
			return jdbc.queryForMap(sql, params.toArray());
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + params);
			}
			return null;
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return null;
		}
	}

	/**
	 * 查询单条记录方法 可能存在多条符合的结果 仅取第一条
	 * 
	 * @param tablename
	 *            表名
	 * @param key
	 *            条件字段名
	 * @param value
	 *            条件值
	 * @return
	 */
	@Transactional()
	public static Map<String, Object> findForMap(String tablename, String key,
			Object value) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put(key, value);
		return findForMap(tablename, map);
	}

	/**
	 * SpringJDBC查询单条记录方法 一般用主键查询 非主键可能存在多条符合的结果 仅取第一条
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map<String, Object> queryForMap(String sql, Object[] args) {
		try {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + StringUtils.arryToString(args));
			}
			return jdbc.queryForMap(sql, args);
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + StringUtils.arryToString(args));
			}
			return null;
		} catch (Exception e) {
			log.error(sql);
			log.error(StringUtils.arryToString(args), e);
			return null;
		}
	}

	/**
	 * 查询单条记录方法 一般用主键查询 非主键可能存在多条符合的结果 仅取第一条
	 * 
	 * @param tablename
	 *            表名
	 * @param key
	 *            字段名
	 * @param value
	 *            值
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map<String, Object> load(String tablename, String key,
			String value) {
		String sql = "select top 1 * from " + tablename + " where " + key
				+ "=?";
		try {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + value);
			}
			return jdbc.queryForMap(sql, new Object[] { value });
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + value);
			}
			return null;
		} catch (Exception e) {
			log.error(sql);
			log.error("[" + key + "=" + value + "]", e);
			return null;
		}
	}

	/**
	 * 查询多条记录方法
	 * 
	 * @param tablename
	 *            表名
	 * @param map
	 *            条件参数MAP 全部满足
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Map<String, Object>> findForList(String tablename,
			Map<String, Object> map) {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from " + tablename + toWhereSql(map, params);

		try {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + params);
			}
			return jdbc.queryForList(sql, params.toArray());
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + params);
			}
			return null;
		} catch (Exception e) {
			log.error(sql);
			log.error(params, e);
			return null;
		}
	}

	public static List<Map<String, Object>> findForList(String tablename,
			String key, Object value) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put(key, value);
		return findForList(tablename, map);
	}

	/**
	 * SpringJDBC查询多条记录方法
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Map<String, Object>> queryForList(String sql,
			Object[] args) {
		try {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + StringUtils.arryToString(args));
			}
			return jdbc.queryForList(sql, args);
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + StringUtils.arryToString(args));
			}
			return null;
		} catch (Exception e) {
			log.error(sql);
			log.error(StringUtils.arryToString(args), e);
			return null;
		}
	}

	/**
	 * SpringJDBC execute方法一般为存储过程
	 * 
	 * @param sql
	 * @throws Exception
	 */
	public static void execute(String sql) throws Exception {
		try {
			if (showSql) {
				log.info("SQL:" + sql);
			}
			jdbc.execute(sql);
		} catch (Exception e) {
			log.error("SQL:" + sql);
			throw e;
		}
	}

	/**
	 * SpringJDBC更新或删除方法
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int update(String sql, Object[] args) {
		try {
			if (showSql) {
				log.info("SQL:" + sql);
				log.info("Params:" + StringUtils.arryToString(args));
			}
			return jdbc.update(sql, args);
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + StringUtils.arryToString(args), e);
			return -1;
		}
	}

	/**
	 * 添加数据
	 * 
	 * @param tablename
	 *            表名
	 * @param map
	 *            待插入数据的Map.key为字段名,value为值
	 * @return 返回影响行数
	 */
	public static int insert(String tablename, Map<String, Object> map) {
		String sql = "insert into " + tablename + " (";
		String sqlv = " values (";
		Set<String> keys = map.keySet();
		List<Object> params = new ArrayList<Object>();
		for (String key : keys) {
			sql += key + ",";
			sqlv += "?,";
			params.add(map.get(key));
		}
		if (sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1) + ")";
		}
		if (sqlv.endsWith("?,")) {
			sqlv = sqlv.substring(0, sqlv.length() - 1) + ")";
		}
		sql += sqlv;
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		try {
			return jdbc.update(sql, params.toArray());
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return -1;
		}
	}

	public static int insertAutoPK(String tablename, Map<String, Object> map) {
		String sql = "insert into " + tablename + " (";
		String sqlv = " values (";
		Set<String> keys = map.keySet();
		List<Object> params = new ArrayList<Object>();
		for (String key : keys) {
			sql += key + ",";
			sqlv += "?,";
			params.add(map.get(key));
		}
		if (sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1) + ")";
		}
		if (sqlv.endsWith("?,")) {
			sqlv = sqlv.substring(0, sqlv.length() - 1) + ")";
		}
		sql += sqlv;
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		try {
			int i = jdbc.update(sql, params.toArray());
			if (i == 1) {
				i = SpringDBHelper.count("select DISTINCT @@IDENTITY from "
						+ tablename, new Object[] {});
			}
			return i;
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return -1;
		}
	}

	public static int insertx(String tablename, Map<String, Object> map) {
		String sql = "insert into " + tablename + " (";
		String sqlv = " values (";
		Set<String> keys = map.keySet();
		List<Object> params = new ArrayList<Object>();
		for (String key : keys) {
			sql += key + ",";
			sqlv += "?,";
			params.add(map.get(key));
		}
		if (sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1) + ")";
		}
		if (sqlv.endsWith("?,")) {
			sqlv = sqlv.substring(0, sqlv.length() - 1) + ")";
		}
		sql += sqlv;
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		return jdbc.update(sql, params.toArray());
	}

	/**
	 * 删除表数据
	 * 
	 * @param tablename
	 * @param map
	 *            主键或删除条件数据
	 * @param pkeys
	 * @return
	 */
	public static int delete(String tablename, Map<String, Object> map) {
		List<Object> params = new ArrayList<Object>();
		String sql = "delete from " + tablename + toWhereSql(map, params);
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		try {
			return jdbc.update(sql, params.toArray());
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return -1;
		}
	}

	public static int delete(String tablename, Map<String, Object> map,
			String... keys) {
		List<Object> params = new ArrayList<Object>();
		String sql = "delete from " + tablename + toWhereSql(map, params, keys);
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		try {
			return jdbc.update(sql, params.toArray());
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return -1;
		}
	}

	/**
	 * 删除表数据
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int delete(String sql, Object[] params) {
		return update(sql, params);
	}

	public static int insert(String sql, Object[] params) {
		return update(sql, params);
	}

	/**
	 * 删除表数据
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int delete(String tablename, String key, Object value) {
		String sql = "delete from " + tablename + " where ";
		if (value == null) {
			return update(sql + key + " is null", new Object[] {});
		} else {
			return update(sql + key + "=?", new Object[] { value });
		}
	}

	/**
	 * 
	 * 更新数据
	 * 
	 * @param tablename
	 *            表名
	 * @param map
	 *            数据 包括主键数据
	 * @param pkeys
	 *            主键字段名
	 * @return
	 */
	public static int update(String tablename, Map<String, Object> map,
			String... pkeys) {
		String sql = "update " + tablename + " set ";
		Set<String> keys = map.keySet();
		List<Object> params = new ArrayList<Object>();
		for (String key : keys) {
			boolean ispk = false;
			for (int i = 0; i < pkeys.length; i++) {
				String pkey = pkeys[i];
				if (pkey.equals(key)) {
					ispk = true;
					break;
				}
			}
			if (!ispk) {
				sql += key + "=?,";
				params.add(map.get(key));
			}
		}
		if (sql.endsWith("=?,")) {
			sql = sql.substring(0, sql.length() - 1) + "";
		}
		sql += toWhereSql(map, params, pkeys);
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		try {
			return jdbc.update(sql, params.toArray());
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return -1;
		}
	}

	/**
	 * 如果数据不存在则添加 存在则更新
	 * 
	 * @param tablename
	 *            表名
	 * @param map
	 *            数据 包括主键数据
	 * @param pkeys
	 *            主键字段名
	 * @return
	 */
	public static int saveOrUpdate(String tablename, Map<String, Object> map,
			String... pkeys) {
		int count = count(tablename, map, pkeys);
		if (count == 0) {
			return insert(tablename, map);
		} else {
			return update(tablename, map, pkeys);
		}
	}

	/**
	 * 统计记录数
	 * 
	 * @param tablename
	 * @param map
	 * @param pkeys
	 * @return
	 */
	public static int count(String tablename, Map<String, Object> map,
			String... pkeys) {
		List<Object> params = new ArrayList<Object>();
		String sql = "select count(*) from " + tablename
				+ toWhereSql(map, params, pkeys);
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + params);
		}
		try {
			return jdbc.queryForInt(sql, params.toArray());
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + params, e);
			return -1;
		}
	}

	/**
	 * 统计记录数
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int count(String sql, Object[] params) {
		if (showSql) {
			log.info("SQL:" + sql);
			log.info("Params:" + StringUtils.arryToString(params));
		}
		try {
			return jdbc.queryForInt(sql, params);
		} catch (Exception e) {
			log.error("SQL:" + sql);
			log.error("Params:" + StringUtils.arryToString(params), e);
			return -1;
		}
	}

	/**
	 * 先删除后添加 常用于关系表
	 * 
	 * @param tablename
	 *            表名
	 * @param list
	 *            要添加的数据
	 * @param key
	 *            删除条件字段
	 * @param value
	 *            删除条件值
	 * @return
	 */
	public static int delInsert(String tablename,
			List<Map<String, Object>> list, String key, Object value) {
		int i = delete(tablename, key, value);
		if (i == -1) {
			return -1;
		}
		i = 0;
		if (list != null) {
			for (Map<String, Object> map : list) {
				i += insert(tablename, map);
			}
			if (i == list.size()) {
				i = 1;
			} else {
				i = -1;
			}
		}
		return i;
	}

	/**
	 * 分页查询
	 * 
	 * @param sqlCount
	 *            统计记录总数sql语句
	 * @param sql
	 *            查询sql语句
	 * @param args
	 *            参数数组
	 * @param pageNo
	 *            当前页
	 * @param pageSize
	 *            每页显示数据条数
	 * @return 当前页面对象CurrentPage
	 */
	public static CurrentPage<Map<String, Object>> fetchPage(
			final String sqlCount, final String sql, final Object args[],
			final int pageNo, final int pageSize) {

		int rowCount = SpringDBHelper.getJdbc().queryForInt(sqlCount, args);

		final CurrentPage<Map<String, Object>> page = new CurrentPage<Map<String, Object>>(
				pageNo, pageSize, rowCount);
		if (showSql) {
			log.info("countSQL:" + sqlCount);
			log.info("SQL:" + sql);
			log.info("Params:" + StringUtils.arryToString(args));
			log.info("pageNo:pageNum:" + pageNo + ":" + page.getPageNum() + "["
					+ page.getBeginRecordNum() + "-" + page.getEndRecordNum()
					+ "]");
		}
		try {
			SpringDBHelper.getJdbc().query(sql, args, new ResultSetExtractor() {
				public Object extractData(ResultSet rs) throws SQLException,
						DataAccessException {
					final List<Map<String, Object>> pageItems = page
							.getPageItems();
					ResultSetMetaData srmd = rs.getMetaData();
					int currentRow = 0;
					while (rs.next()
							&& currentRow < page.getBeginRecordNum() + pageSize) {
						if (currentRow >= page.getBeginRecordNum()) {
							int i = srmd.getColumnCount();
							Map<String, Object> map = new HashMap<String, Object>();
							while (i > 0) {
								try {
									map.put(srmd.getColumnLabel(i),
											rs.getObject(i));
								} catch (Exception e) {
									map.put(srmd.getColumnLabel(i), null);
								}
								i--;
							}
							pageItems.add(map);
						}
						currentRow++;
					}
					return page;
				}
			});
			return page;
		} catch (Exception e) {
			log.error("countSQL:" + sqlCount);
			log.error("SQL:" + sql);
			log.error("Params:" + StringUtils.arryToString(args));
			log.error(
					"pageNo:pageNum:" + pageNo + ":" + page.getPageNum() + "["
							+ page.getBeginRecordNum() + "-"
							+ page.getEndRecordNum() + "]", e);
			return null;
		}
	}

}
